package com.guangbao.utils;

import java.io.IOException;
import java.sql.*;
import java.util.*;

/**
 * 连接数据库工具类
 */
public class JdbcUtil {
	private static String driver = "";
	private static String url = "";
	private static String username = "";
	private static String password = "";

	static {// 静态代码块注册驱动
		try {
			Properties properties = new Properties();
			// 加载配置文件
			properties.load(JdbcUtil.class.getResourceAsStream("/db.properties"));

			driver = properties.getProperty("jdbc.driver");
			url = properties.getProperty("jdbc.url");
			username = properties.getProperty("jdbc.username");
			password = properties.getProperty("jdbc.password");

			Class.forName(driver);// 加载驱动

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	// 返回连接
	public static Connection getConnection() {
		try {
			return DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	// 使用动态参数，更新数据-->插入记录，更新记录，删除记录（不需要返回）
	public static Integer executeUpdate(String sql, Object... params) {//
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			if (null != params)
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(ps);
			close(conn);
		}
		return -1;
	}

	public static Integer executeCount(String sql) {// 返回表中有多少条记录
		Integer count = 0;
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			rs.next();
			count = rs.getInt(1);

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	//执行查询 返回结果集
	@SuppressWarnings("unchecked")
	public static List<Map<String, Object>>  executeQuery(String sql, Object... params) {// 查询数据库，需要返回
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			if (null != params)
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i].toString());
				}
			rs = ps.executeQuery();// 获得结果集
			ResultSetMetaData md = rs.getMetaData();
	        int columnCount = md.getColumnCount();
	        while (rs.next()) {
	            Map<String, Object> rowData = new HashMap<String, Object>();
	            for (int i = 1; i <= columnCount; i++) {
	                rowData.put(md.getColumnName(i), rs.getObject(i));
	            }
	            list.add(rowData);
	        }
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(ps);
			close(conn);
		}
		return list;
	}

    //关闭链接操作
	public static void close(Connection conn) {
		if (null != conn) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 关闭Statement-->发送sql语句
	public static void close(Statement stat) {
		if (null != stat) {
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 关闭ResultSet
	public static void close(ResultSet rs) {
		if (null != rs) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}